﻿Imports System.IO
Imports iTextSharp.tool.xml.pipeline.css
Imports iTextSharp.tool.xml.pipeline.end
Imports iTextSharp.tool.xml.pipeline.html
Imports iTextSharp.tool.xml.html
Imports iTextSharp.text.html.simpleparser
Imports iTextSharp.tool.xml.parser
Imports System.Net.Mail
Imports StartFrame.Web.BR.Helpers
Imports StartFrame.Web.BR
Imports System.Text
Imports iTextSharp.tool.xml

Namespace Consultas


    'Clase para la tabla Sucursales
    Public Class Recorridos
        Inherits JsonAbm

        ''' <summary>
        ''' Esta clase se encarga de la consulta de los recorridos.
        ''' </summary>
        Sub New()
            MyBase.New(Operador, Password, "XRCONSULTARECORRIDOS", "nuevositio.dbo.acceso_recorrida", "id")
            MyBase.lnk_nombreOperador = Common.Env.Operador
        End Sub

        Public Function ObtenerTipoRecorrida(cdOperador As String) As String
            Return DA.Sql.Search(Common.Env.GetConnectionString("cnnNuevoSitioKey"), "tpRecorrida", "Accesos", String.Format("cd_operador = '{0}'", cdOperador)).ToString()
        End Function

        Public Function ObtenerDias(cdOperador As String) As DataTable
            Return DA.Sql.Select(Common.Env.ConnectionString, "distinct TOP 5 dbo.FechaSinHora(FechaAsignado) as FechaAsignado", "Recorridas_Asignadas", String.Format(" FechaAsignado >= dbo.FechaSinHora(GETDATE()) AND Baja = 0 AND idProcurador = dbo.fn_ObtenerIdProcurador('{0}')", cdOperador), "FechaAsignado").Tables(0)
        End Function

        Public Function ObtenerRecorridas(cdOperador As String) As DataTable
            Return DA.Sql.Select(Common.Env.ConnectionString, "jP.id, jp.descripcion", "judiciales.dbo.Parametros jP INNER JOIN nuevositio.dbo.acceso_recorrida ar ON jP.id = ar.idRecorrida", String.Format("idAcceso = recorridas.dbo.fn_ObtenerIdProcurador('{0}')", cdOperador)).Tables(0)
        End Function

        Public Function ObtenerListadoRecorridas(fechas As String, idRecorrida As String) As List(Of Recorrida)
            Dim ds As DataSet = Nothing
            If Not String.IsNullOrWhiteSpace(fechas) Then
                ds = DA.Sql.Select(Common.Env.ConnectionString, "DISTINCT CJ.juzgado, CJ.caratula, CJ.idExpediente", "Recorridas_Asignadas RA INNER JOIN cubojudiciales.dbo.controljudiciales CJ ON RA.idExpediente = CJ.idExpediente", String.Format("idProcurador = dbo.fn_ObtenerIdProcurador('{0}') AND FechaAsignado IN ({1})", Common.Env.Operador, fechas), "CJ.juzgado, CJ.caratula, CJ.idExpediente")
            End If
            If Not String.IsNullOrWhiteSpace(idRecorrida) Then
                ds = DA.Sql.Select(Common.Env.ConnectionString, "DISTINCT CJ.juzgado, CJ.caratula, CJ.idExpediente", "Recorridas_Asignadas RA INNER JOIN cubojudiciales.dbo.controljudiciales CJ ON RA.idExpediente = CJ.idExpediente", String.Format("idProcurador = dbo.fn_ObtenerIdProcurador('{0}') AND idRecorrida = {1}", Common.Env.Operador, idRecorrida), "CJ.juzgado, CJ.caratula, CJ.idExpediente")
            End If

            Dim retList As New List(Of Recorrida)
            If ds.Tables.Count = 1 Then
                For Each dr As DataRow In ds.Tables(0).Rows
                    retList.Add(New Recorrida(dr("idExpediente")))
                Next
                Return retList
            End If
            Return Nothing
        End Function

        Public Sub CrearPdf(mStream As Stream, recorridas As List(Of Recorrida), Optional closeStream As Boolean = True)
            Dim idoc As New iTextSharp.text.Document(iTextSharp.text.PageSize.A4)
            Dim pdfw As iTextSharp.text.pdf.PdfWriter = iTextSharp.text.pdf.PdfWriter.GetInstance(idoc, mStream)

            idoc.Open()

            Try
                Dim htmlContext As New HtmlPipelineContext(Nothing)
                htmlContext.SetTagFactory(Tags.GetHtmlTagProcessorFactory())
                Dim cssResolver = XMLWorkerHelper.GetInstance().GetDefaultCssResolver(True)
                Dim pipe As New CssResolverPipeline(cssResolver, New HtmlPipeline(htmlContext, New PdfWriterPipeline(idoc, pdfw)))
                Dim worker As New XMLWorker(pipe, True)
                Dim p As New XMLParser(worker)

                p.Parse(New StringReader(ObtenerHTML(recorridas)))

            Catch ex As Exception

            End Try
            pdfw.CloseStream = closeStream
            idoc.Close()
        End Sub

        Public Sub EnviarEmail(mStream As Stream, nombreArchivo As String)
            Dim param As New StartFrame.BR.Utilitarios.Parametros
            Dim sClient As New System.Net.Mail.SmtpClient(param.getParametro("MAILS_SMTP"))
            Dim mail As New System.Net.Mail.MailMessage()
            mail.From = New MailAddress(param.getParametro("MAILS_FROM"))
            mail.To.Add(DA.Sql.Search(Common.Env.ConnectionString, String.Format("SELECT email FROM dbo.Accesos WHERE id = dbo.fn_ObtenerIdProcurador('{0}')", Common.Env.Operador)))
            mStream.Position = 0
            mail.Attachments.Add(New Attachment(mStream, nombreArchivo))
            sClient.Send(mail)
        End Sub

        Private Function ObtenerHTML(ListaRecorridas As List(Of Recorrida)) As String
            Dim sb As New StringBuilder()
            sb.Append("<html><body>")
            For Each rcrd As Recorrida In ListaRecorridas
                sb.Append(CrearCabecera(rcrd))
                sb.Append(CrearCaratula(rcrd))
                sb.Append(CrearDetalles(rcrd))
                sb.Append(CrearCuerpo(rcrd))
            Next
            sb.Append("</body></html>")
            Return sb.ToString()
        End Function

        Public Class Recorrida
            Sub New(idExpediente As String)
                Dim dsDatos As DataSet = DA.Sql.ExecSPDS(Common.Env.GetConnectionString("cnnkey"), "wp_DatosPDF", New ArrayList() From {idExpediente, Common.Env.Operador})

                Try
                    Me.Vistas = dsDatos.Tables(0).Rows(0)("vistas").ToString()
                    Me.Estado = dsDatos.Tables(0).Rows(0)("estado").ToString()
                Catch ex As Exception
                    Me.Vistas = 0
                    Me.Estado = ""
                    'Throw New Exception("Falta información para el armado del PDF: 'cubojudiciales.dbo.controljudiciales'", ex)
                End Try

                Try
                    Me.Juzgado = dsDatos.Tables(1).Rows(0)("juzgado").ToString()
                    Me.Domicilio = dsDatos.Tables(1).Rows(0)("domicilio").ToString()
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Juzgados'", ex)
                End Try

                Try
                    Me.Departamento = dsDatos.Tables(2).Rows(0)("descripcion").ToString()
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Departamentos'", ex)
                End Try

                Try
                    Me.NroExpediente = dsDatos.Tables(3).Rows(0)("NroExpediente").ToString()
                    Me.Caratula = dsDatos.Tables(3).Rows(0)("Caratula").ToString()
                    Dim dtTemp As DateTime
                    If DateTime.TryParse(dsDatos.Tables(3).Rows(0)("FechaInicio"), dtTemp) Then
                        Me.FechaInicio = dtTemp
                    End If
                    Me.Paralizado = dsDatos.Tables(3).Rows(0)("Paralizado").ToString()
                    Me.NroReceptoria = dsDatos.Tables(3).Rows(0)("NroReceptoria").ToString()
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Caratulas'", ex)
                End Try


                Try
                    If dsDatos.Tables(4).Rows.Count > 0 Then
                        Me.UltimoEstado = New Registros() With {
                        .Fecha = dsDatos.Tables(4).Rows(0)("fechalog"),
                        .Descripcion = dsDatos.Tables(4).Rows(0)("Descripcion").ToString()
                        }
                    End If
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Historia' (ultimo estado)", ex)
                End Try

                Try
                    Me.UltimosMovimientos = (From dUM As DataRow In dsDatos.Tables(5).Rows
                                            Select New Registros() With {
                                                .Fecha = dUM("fecha"),
                                                .Descripcion = dUM("Descripcion").ToString()
                                            }).ToList()
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Historia' (ultimos movimientos)", ex)
                End Try

                Try
                    Me.UltimosDespachos = (From dUV As DataRow In dsDatos.Tables(6)
                                           Select New Registros() With {
                                               .Fecha = dUV("fecha"),
                                               .Descripcion = dUV("Descripcion").ToString(),
                                                .Foja = dUV("fojas").ToString()
                                           }).ToList()
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Historia' (despachos/escritos/notificaciones/diligenciamientos)", ex)
                End Try

                Try
                    Me.UltimasRecorridas = (From dUR As DataRow In dsDatos.Tables(7)
                                          Select New Registros() With {
                                              .Fecha = dUR("fecha"),
                                              .Descripcion = dUR("Descripcion").ToString()
                                          }).ToList()
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Historia' (ultimas recorridas)", ex)
                End Try

                Try
                    If dsDatos.Tables(8).Rows.Count > 0 Then
                        Me.ProximoPaso = New Registros() With {
                            .Fecha = dsDatos.Tables(8).Rows(0)("fecha"),
                            .Descripcion = dsDatos.Tables(8).Rows(0)("Descripcion").ToString()
                            }
                    End If
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.Agenda_Procesos'", ex)
                End Try

                Try
                    Me.MensajeCliente = (From dMC As DataRow In dsDatos.Tables(9).Rows
                                                        Select New Registros() With {
                                                            .Fecha = dMC("fecha"),
                                                            .Descripcion = dMC("texto").ToString()
                                                        }).ToList()
                Catch ex As Exception
                    Throw New Exception("Falta información para el armado del PDF: 'judiciales.dbo.mensajeria'", ex)
                End Try

            End Sub

            Sub New()
            End Sub

            Public Class Registros
                Public Property Fecha As DateTime
                Public Property Descripcion As String
                Public Property Foja As String
            End Class

            Public Property Juzgado As String
            Public Property Departamento As String
            Public Property Domicilio As String
            Public Property NroExpediente As String
            Public Property Caratula As String
            Public Property FechaInicio As Nullable(Of DateTime)
            Public Property Paralizado As String
            Public Property Vistas As Integer
            Public Property NroReceptoria As String
            Public Property Estado As String
            Public Property UltimoEstado As Registros
            Public Property UltimosMovimientos As List(Of Registros)
            Public Property UltimosDespachos As List(Of Registros)
            Public Property UltimasRecorridas As List(Of Registros)
            Public Property ProximoPaso As Registros
            Public Property MensajeCliente As List(Of Registros)
        End Class

        Private Function CrearCabecera(rcrd As Recorrida) As String
            Dim sb As New StringBuilder
            sb.AppendFormat("<table style='background-color: #a9a9a9; border: 1;' width='100%'><tr><td style='font-size: 12; font-family: Arial Black;'>{0} - {1}</td></tr><tr><td style='font-size: 10; font-family: Arial;'>{2}</td></tr></table>", rcrd.Juzgado, rcrd.Departamento, rcrd.Domicilio)
            Return sb.ToString()
        End Function

        Private Function CrearCaratula(rcrd As Recorrida) As String
            Dim sb As New StringBuilder
            sb.AppendFormat("<table style='border: 1;' width='100%'><tr><td style='font-size: 10; font-family: Arial;'>Caratula: <B>({0}) {1}</B></td></tr></table>", rcrd.NroExpediente, rcrd.Caratula)
            Return sb.ToString()
        End Function

        Private Function CrearDetalles(rcrd As Recorrida) As String
            Dim sb As New StringBuilder
            Dim fechaInicio As String = String.Empty
            If (rcrd.FechaInicio.HasValue) Then
                fechaInicio = rcrd.FechaInicio.Value.ToShortDateString()
            End If
            sb.AppendFormat("<table style='border: 1;' width='100%'><tr><td style='font-size: 10; font-family: Arial;'>Fecha Inicio: {0} Leg. Paralizado: {1} Cant. Vistas: {2} Nro.Receptoria: {3} Estado: {4}</td></tr></table>", fechaInicio, rcrd.Paralizado, rcrd.Vistas, rcrd.NroReceptoria, rcrd.Estado)
            Return sb.ToString()
        End Function

        Private Function CrearCuerpo(rcrd As Recorrida) As String
            Dim sb As New StringBuilder
            sb.Append("<table width='100%' style='font-size: 10; font-family: Arial;'>")
            sb.Append("<tr><td><b>Observaciones:</b></td></tr>")

            sb.Append("<tr><td><table width='100%' style='font-size: 10; font-family: Arial;'>")
            sb.Append("<tr><td><b>Ultimo Estado:</b></td><td><b>Ultimos Movimientos:</b></td></tr>")
            sb.Append("<tr><td>")
            If Not rcrd.UltimoEstado Is Nothing Then
                sb.AppendFormat("({0}) {1}", rcrd.UltimoEstado.Fecha.ToShortDateString(), rcrd.UltimoEstado.Descripcion)
            End If
            sb.Append("</td><td>")
            If Not EsNuloOVacio(rcrd.UltimosMovimientos) Then
                sb.AppendFormat("({0}) {1}", rcrd.UltimosMovimientos(0).Fecha.ToShortDateString(), rcrd.UltimosMovimientos(0).Descripcion)
            End If
            sb.Append("</td></tr>")

            sb.Append("<tr><td></td><td>")
            If Not EsNuloOVacio(rcrd.UltimosMovimientos, 1) Then
                sb.AppendFormat("({0}) {1}", rcrd.UltimosMovimientos(1).Fecha.ToShortDateString(), rcrd.UltimosMovimientos(1).Descripcion)
            End If
            sb.Append("</td></tr></table></td></tr>")

            If Not EsNuloOVacio(rcrd.UltimosDespachos) Then
                sb.Append("<tr><td><table width='100%' style='font-size: 10; font-family: Arial;'>")
                sb.Append("<tr><td><b>Ultimos despachos/escritos/notificaciones/diligenciamientos</b></td></tr>")
                sb.Append("<tr><td>Principal</td></tr>")
                For Each uDespacho As Recorrida.Registros In rcrd.UltimosDespachos
                    sb.AppendFormat("<tr><td>({0}) {1} Foja {2}</td></tr>", uDespacho.Fecha.ToShortDateString(), uDespacho.Descripcion, IIf(String.IsNullOrEmpty(uDespacho.Foja), "SN", uDespacho.Foja))
                Next
                sb.Append("</table></td></tr>")
            End If

            If Not EsNuloOVacio(rcrd.UltimasRecorridas) Then
                sb.Append("<tr><td><table width='100%' style='font-size: 10; font-family: Arial;'>")
                sb.Append("<tr><td><b>Ultimas recorridas</b></td></tr>")
                For Each uRecorrida As Recorrida.Registros In rcrd.UltimasRecorridas
                    sb.AppendFormat("<tr><td>({0}) {1}</td></tr>", uRecorrida.Fecha.ToShortDateString(), uRecorrida.Descripcion)
                Next
                sb.Append("</table></td></tr>")
            End If

            If rcrd.ProximoPaso IsNot Nothing Then
                sb.Append("<tr><td><table width='100%' style='font-size: 10; font-family: Arial;'>")
                sb.AppendFormat("<tr><td><b>Proximo Paso:</b> ({0}) Recorrida Periodica - {1}</td></tr>", rcrd.ProximoPaso.Fecha.ToShortDateString(), rcrd.ProximoPaso.Descripcion)
                sb.Append("</table></td></tr>")
            End If

            If Not EsNuloOVacio(rcrd.MensajeCliente) Then
                sb.Append("<tr><td><table width='100%' style='font-size: 10; font-family: Arial;'>")
                sb.Append("<tr><td><b>Mensaje Cliente</b></td></tr>")
                For Each mCliente As Recorrida.Registros In rcrd.MensajeCliente
                    sb.AppendFormat("<tr><td>({0}) {1}</td></tr>", mCliente.Fecha.ToShortDateString(), mCliente.Descripcion)
                Next
                sb.Append("</table></td></tr>")
            End If

            sb.Append("</table>")
            Return sb.ToString()
        End Function

        Private Function EsNuloOVacio(obj As Object, Optional posicion As Integer = 0) As Boolean
            If obj Is Nothing Then
                Return True
            End If
            If Not TypeOf obj Is ICollection Then
                Return True
            End If
            If CType(obj, ICollection).Count = 0 Then
                Return True
            End If
            If CType(obj, ICollection).Count <= posicion Then
                Return True
            End If
            Return False
        End Function
    End Class
End Namespace